/*
 * Copyright 2009 Zero Separation
 *
 *     This file is part of PDSSQLService.
 *
 *  PDSSQLService is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  PDSSQLService is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with PDSSQLService.  If not, see <http://www.gnu.org/licenses/>.
 *
 */

package com.zero_separation.pds.sql;

import com.sun.sgs.app.AppContext;
import com.sun.sgs.app.ManagedObject;
import com.sun.sgs.app.ManagedReference;
import com.sun.sgs.kernel.ComponentRegistry;
import com.sun.sgs.service.Service;
import com.sun.sgs.service.TransactionProxy;
import com.sun.sgs.services.app.AsyncCallable;
import com.sun.sgs.services.app.AsyncRunnable;
import com.sun.sgs.services.app.AsyncTaskCallback;
import com.sun.sgs.services.app.TransactionRunner;
import com.sun.sgs.services.impl.service.AsyncTaskService;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author Tim
 */
public class SQLServiceImpl implements SQLService, Service {

    private static final Logger logger = Logger.getLogger(SQLServiceImpl.class.getName());

    private AsyncTaskService asyncTaskService;

    public SQLServiceImpl(Properties properties, ComponentRegistry componentRegistry, TransactionProxy transProxy) {
        asyncTaskService = transProxy.getService(AsyncTaskService.class);
    }

    public SQLConnection createConnection(String driver, String connectionURL) {
        return new SQLConnectionImpl(driver, connectionURL);
    }

    public void performQuery(SQLConnection connection, SQLStatement statement, SQLResultHandler resultHandler) {
        if (connection == null)
            throw new NullPointerException("The connection cannot be null");
        if (!statement.isValid())
            throw new IllegalArgumentException("The SQLStatement specified is not valid: "+statement);

        // Make a defensive copy to ensure that changes to the statement after we return don't alter this query
        SQLStatement statementCopy = new SQLStatement(statement);

        if (resultHandler != null) {
            logger.log(Level.FINE, "Performing SQL Query with result handler: {0}", statementCopy);
            asyncTaskService.startTask(new NonTransactionalProcessor(connection, statementCopy), new ResultWrapper(resultHandler));
        } else {
            logger.log(Level.FINE, "Performing SQL Query without result handler: {0}", statementCopy);
            asyncTaskService.startTask(new NonTransactionalProcessor(connection, statementCopy));
        }
    }

    public String getName() {
        return getClass().getName();
    }

    public void ready() throws Exception {
    }

    public void shutdown() {
    }

    private static class NonTransactionalProcessor implements AsyncRunnable, AsyncCallable<SQLResult>, Serializable {

        SQLStatement statement;
        SQLConnection connection;

        public NonTransactionalProcessor(SQLConnection connection, SQLStatement statement) {
            this.statement = statement;
            this.connection = connection;
        }

        public SQLResult call(TransactionRunner transRunner) {
            Connection conn=null;

            try {

                // Set up the correct database driver for the SQL Server
                // JDBC drivers should register themselves when loaded.
                Class.forName(connection.getDriver());

                // Connect to the database and prepare the statement
                conn = DriverManager.getConnection( connection.getConnectionURL() );
            } catch (Exception ex) {
                return new SQLResult(SQLResult.Result.CONNECTION_FAILURE, null, null, -1, ex, statement);
            }

            PreparedStatement query=null;

            try {

                if (statement.getSQLQueryFlags() != 0) {
                    query = conn.prepareStatement(statement.getStatement(),
                                     statement.getSQLQueryFlags());
                } else {
                    query = conn.prepareStatement(statement.getStatement());
                }
                List<SQLParam> params = statement.getParams();
                for (int i=0;i<params.size();i++) {
                    int type = params.get(i).getType();
                    if (type == java.sql.Types.VARCHAR)
                        query.setString(i+1, (String)params.get(i).getOb());
                    else
                        query.setObject(i+1, params.get(i).getOb(), type);
                }

                logger.log(Level.FINER, "Executing SQL Statement (Flags: {0}): {1}", new Object[]{statement.getSQLQueryFlags(), statement});
                // Execute the query and process the results
                if (query.execute()) {
                    return new SQLResult(
                            SQLResult.Result.SUCCESS,
                            query.getResultSet(),
                            ((statement.getSQLQueryFlags() == Statement.RETURN_GENERATED_KEYS) ? query.getGeneratedKeys() : null),
                            -1,
                            null,
                            statement);
                } else {
                    return new SQLResult(
                            SQLResult.Result.SUCCESS,
                            null,
                            ((statement.getSQLQueryFlags() == Statement.RETURN_GENERATED_KEYS) ? query.getGeneratedKeys() : null),
                            query.getUpdateCount(),
                            null,
                            statement);
                }
            } catch (Exception ex) {
                return new SQLResult(SQLResult.Result.SQL_FAILURE, null, null, -1, ex, statement);
            } finally {
                if (query != null)
                    try {
                        query.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(SQLServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
                    }

                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        public void run(TransactionRunner transRunner) {
            SQLResult result = call(transRunner);
            if (result.getResult() == SQLResult.Result.SUCCESS)
                logger.log(Level.FINER, "SQL Statement with no result handler succeeded: {0}", result.getQuery());
            else
                logger.log(Level.WARNING, "SQL Statement with no result handler failed: {0}, {1}, {2}", new Object[]{result.getQuery(), result.getResult(), result.getFailure()});
        }

    }

    /** Wrapper so that a Serializable can be managed. */
    private static class ResultWrapper implements Serializable, AsyncTaskCallback<SQLResult>
    {
        private final static long serialVersionUID = 1L;
        private final ManagedReference<SQLResultHandler> managedRef;
        private final SQLResultHandler ref;

        ResultWrapper(SQLResultHandler callback) {
            if (callback instanceof ManagedObject) {
                managedRef = AppContext.getDataManager().createReference(callback);
                ref = null;
            } else {
                managedRef = null;
                ref = callback;
            }
        }

        public void notifyResult(SQLResult arg0) {
            // This is a transactional task and may get retried. Reset the result
            // set each time it is called to make sure it goes in from its initial
            // position each time.
            if (arg0.getResultSet() != null)
                try {
                    arg0.getResultSet().beforeFirst();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "SQL Exception resetting result set", ex);
                }

            if (arg0.getGeneratedKeys() != null)
                try {
                    arg0.getGeneratedKeys().beforeFirst();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "SQL Exception resetting generated keys", ex);
                }

            if (managedRef == null)
                ref.SQLQueryResult(arg0);
            else
                managedRef.get().SQLQueryResult(arg0);
        }

        public void notifyFailed(Throwable arg0) {
            logger.log(Level.SEVERE, "Exception received from AsyncTaskService processing SQL query", arg0);
        }
    }

}
